package fr.web325.dao;

import fr.web325.bean.Order;
import fr.web325.bean.User;
import fr.web325.utils.QueryRunnerUtils;
import fr.web325.bean.Book;
import fr.web325.bean.Notice;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.BeanMapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ClientDao {
    // 获取最新公告
    public Notice getNewNotice() throws SQLException {
        // 获取数据库连接池对象
        QueryRunner qr = new QueryRunner(QueryRunnerUtils.getDateSource());
        return qr.query("select * from notice order by n_time desc limit 0,1", new BeanHandler<>(Notice.class));
    }

    // 根据分类查询书籍列表
    public List<Book> getBookListByCategory(String category,int currentPage, int currentSize) throws SQLException {
        // 获取数据库连接池对象
        QueryRunner qr = new QueryRunner(QueryRunnerUtils.getDateSource());
        String sql = "select * from products where category=? limit ?,?";
        if(category.equals("all")){
            sql = "select * from products limit ?,?";
            return qr.query(sql, new BeanListHandler<>(Book.class),(currentPage - 1) * currentSize,currentSize);
        }else{
            // 查询并返回List对象
            return qr.query(sql, new BeanListHandler<>(Book.class),category,(currentPage - 1) * currentSize,currentSize);
        }
    }

    // 获取本周热卖
    public List<Map<String,Object>> getHotList() throws SQLException {
        // 获取数据库连接池对象
        QueryRunner qr = new QueryRunner(QueryRunnerUtils.getDateSource());
        String sql = "select products.id,products.imgurl,products.name,sum(orderitem.buynum) totalsnum from orders,products,orderitem where orders.id = orderitem.order_id and orderitem.product_id = products.id and orders.paystate=1 group by products.id order by totalsnum desc limit 0,3";
        return qr.query(sql,new MapListHandler());
    }

    // 用户搜索查询
    public List<Map<String,Object>> searchByContent(String content) throws SQLException {
        // 获取数据库连接池对象
        QueryRunner qr = new QueryRunner(QueryRunnerUtils.getDateSource());
        content = "%"+content+"%";
        String sql = "select * from products where name like ?";
        return qr.query(sql,new MapListHandler(),content);
    }

    // 根据分类获取当前分类的总数
    public String getBookTotalByCategory(String category)throws SQLException{
        // 获取数据库连接池对象
        QueryRunner qr = new QueryRunner(QueryRunnerUtils.getDateSource());
        String sql = "select count(*) as total from  products where category = ?";
        RecordCount rc = null;
        if(category.equals("all")){
            sql = "select count(*) as total from  products";
            rc = qr.query(sql, new BeanHandler<>(RecordCount.class));
        }else{
            rc = qr.query(sql, new BeanHandler<>(RecordCount.class),category);
        }
        return rc.getTotal();
    }

    // 生成订单插入到orders表中
    public Boolean addOrder(Order order) throws SQLException {
        // 获取数据库连接池对象
        QueryRunner qr = new QueryRunner(QueryRunnerUtils.getDateSource());
        return qr.update("insert into orders values (?,?,?,?,?,?,?,?)",order.getId(),order.getMoney(), order.getReceiverAddress(), order.getReceiverName(), order.getReceiverPhone(), order.getPaystate(), order.getOrderTime(), order.getUser_id()) > 0;
    }

    // 将订单详细信息记录到orderitem表中
    public void addOrderItem(String order_id, HashMap<String, Integer > list)throws SQLException{
        QueryRunner qr = new QueryRunner(QueryRunnerUtils.getDateSource());
        // 使用Iterator迭代器
        for (Map.Entry<String, Integer> entry : list.entrySet()) {
            // 书本ID跟数量 循环插入
            qr.update("insert into orderitem values (?,?,?)", order_id, entry.getKey(), entry.getValue());
        }
    }

    // 通过订单ID获取已提交信息
    public List<Map<String,Object>> getOrderItem(String order_id)throws SQLException{
        // 获取数据库连接池对象
        QueryRunner qr = new QueryRunner(QueryRunnerUtils.getDateSource());
        // Book对象 - 数量 - orderItem
        String sql = "select ordertime,order_id,orders.money,receiverAddress,receiverName,receiverPhone,buynum,name,price,imgurl from orders,orderitem,products where orders.id = ? and orders.id = orderitem.order_id and products.id = orderitem.product_id";
        List<Map<String,Object>> maps = qr.query(sql,new MapListHandler(),order_id);
//        for (Map<String, Object>map : maps) {
//            System.out.println(map);
//        }
        return maps;
    }

    // 通过订单ID修改支付状态
    public Boolean editOrderSuccess(String order_id)throws SQLException{
        // 获取数据库连接池对象
        QueryRunner qr = new QueryRunner(QueryRunnerUtils.getDateSource());
        return qr.update("update orders set paystate = 1 where id = ?",order_id) > 0;
    }

    // 通过用户ID获取对应的订单列表
    public List<Map<String,Object>> getMineOrders(int userId) throws SQLException {
        // 获取数据库连接池对象
        QueryRunner qr = new QueryRunner(QueryRunnerUtils.getDateSource());
        String sql = "select * from orders where user_id = ?";
        return qr.query(sql,new MapListHandler(),userId);
    }

    // 通过用户ID修改用户信息
    // 根据ID修改用户
    public Boolean uploadUser(User user) throws SQLException {
        // 获取数据库连接池对象
        QueryRunner qr = new QueryRunner(QueryRunnerUtils.getDateSource());
        return qr.update("update user set username=?,PASSWORD=?,gender=?,email=?,telephone=?,introduce=? where id=?",user.getUsername(),user.getPASSWORD(),user.getGender(),user.getEmail(),user.getTelephone(),user.getIntroduce(),user.getId()) > 0;
    }

    // 通过id获取用户信息
    public User getUserById(int id)throws SQLException{
        QueryRunner qr =new QueryRunner(QueryRunnerUtils.getDateSource());
        return  qr.query("select * from user where id = ?",new BeanHandler<>(User.class),id);
    }
    public static class RecordCount {
        //当前页的页码
        private String total;

        public RecordCount() {
        }

        public String getTotal() {
            return total;
        }

        public void setTotal(String total) {
            this.total = total;
        }
    }
}
